The Financial Data Manager project is a project created by two Computer Science majors also pursuing minors in General Business. The purpose of this project is to aid investors in making the best investment choices in the stock market. Our database provides the necessary financial data that an investor can find highly useful to evaluate a company’s financial health and wealth, and we believe that a database will not only reduce time, but also get future investors a chance to invest utilizing a wise tool to aid them :).
Problem domain: Creating a database with this data would address a problem faced by investors and financial analysts. Companies can have different means of reporting which makes it inefficient and time-consuming to look at. Specifically, finding and retrieving the exact information is a waste of time for investors who want to get their data as soon as possible to make investment decisions, especially with the fast-paced and changing nature of the markets. For example, I had to look for information on multiple website wasting around 3-4 hours of time just searching for the most accurate information in order to make stock pitches for the Student Managed Investment Portfolio (SMIP) which is an investment club at VCU.
Need: A database is needed to store and organize financial information and is useful for quicker decision-making processes and better management of all the existing data.
Context, scope, and perspective: The database is useful for investors and for those who want to get experience reading financial statements to make investment decisions. It takes a long time to look for accurate information about a company and we thought it would be better to have all that important information in one place for users to access that data. For example, if I wanted to decide whether or not to invest in Nvidia, I would first take a look at financial statements to see the financial health of the company to see how they’re doing and then decide whether or not the stock would be a buy, sell, or hold.
User roles and use cases: The consumer target is primarily investors in the stock market whether it is a millionaire or a novice who is just getting started. These are the people who will mostly be using our database to fetch financial data for a company they wish to know more about. For example, if an investor wants to get the earningsfor a company like Apple, they can use the database to search the company and find the earnings at a specific point of time. If an investor decides whether or not buying Apple stock would be a good decision, they can use earnings as one of the indicators to make that decision utilizing the database with the data we provide. The database will be use an API that will fetch data from a trusted source to store it in the database.
Security and Privacy: The only security concern that we could think of is whether or not the information from the source we get it from is accurate. Another issue could be that in case there is a data breach and all information is lost, then it will be harder for our customers to gain access to essential information.
Entity-relationship diagrams: (Note: We made the attributes shorter to make the diagram more readable.)
Chen diagram
Figure 1: Financial Data Manager in Chen Notation
Crows foot diagram:
erDiagram
COMPANY {
string ticker
string name
string sector
string industry
}
INCOME_STATEMENT {
int statement_id1
string ticker
date fiscal_year_end
float total_revenue
float cost_of_revenue
float gross_profit
float operating_income
float Net_income_common_stockholders
float income_before_tax
float ebit
float ebitda
}
STOCK {
string ticker
int company_id
float price
date IPO_date
}
BALANCE_SHEET {
string ticker
int statement_id2
date fiscal_year_end
float total_assets
float current_assets
float cash
float cash_and_cash_equivalents
float accounts_receivables
float total_debt
float net_tangible_assets
float long_term_debt
float working_capital
float invested_capital
float tangible_book_value
float total_capitalization
int shares_issued
float stockholders_equity
float retained_earnings
float common_stock_equity
}
CASH_FLOW {
int statement_id3
string ticker
date fiscal_year_end
float operating_cashflow
float capital_expenditures
float free_cash_flow
float cash_dividends_paid
}
EARNINGS {
int earnings_id
string ticker
int earnings_id
int company_id
date fiscal_date_end
float reported_eps
}
COMPANY ||--o{ INCOME_STATEMENT : publishes
COMPANY ||--o{ STOCK : issues
COMPANY ||--o{ BALANCE_SHEET : records
COMPANY ||--o{ CASH_FLOW : flows
COMPANY ||--o{ EARNINGS : reports
Web-interface design
We chose to implement the web interface using React.js and PHP. PHP was chosen because it is highly compatible with the SQL server we have used all semester for this course, phpMyAdmin. I also have experience using React.js, and it allows us to build a dynamic and user friendly front-end page. PHP is more simple and efficient to implement than other back end development choices as well. The combination of these two development tools helped us create a responsive and interactive web application.
Screenshots
Image 1 - Main Screen
This image displays the default page of our application that runs when accessing “http://localhost:x/” (we used port 3000).
Image 2 - Creating a New Record
This image shows the web interface screen for creating a new record. In this example, it is creating a new record in the ‘Company’ table.
Image 3 - Editing an Existing Record
This image shows the web interface screen for updating an existing record. In this example, it is editing ‘Eod_price’ attribute of a record in the ‘Stock’ table.
Image 4 - Deleting an Existing Record
This image shows that records can be deleted using its primary key. In this example, it is deleting a record from the ‘Stock’ table, and its stock ticker is ‘AAPL’.
Image 5 - Running a Query
This image shows how the menu of queries can be ran. There is a dropdown selection of all possible reports to view. In this example, the first query is ran, and it shows all records in the ‘Company’ table and their corresponding tickers.
Image 6 - Filtering a Table
This image shows how each table can be filtered. in this example, I wanted to see all records in the Technology sector.
Reports
SQL DDL
No errors detected while loading: ./my-ddl.sql
20 queries:
List all companies and their stock tickers.
sql ="""SELECT Name, Ticker FROM Company;"""run_sql_and_return_df(sql, cnx)
Name
Ticker
0
Apple Inc.
AAPL
1
Amazon.com Inc.
AMZN
2
Alphabet Inc.
GOOGL
3
Meta Platforms Inc.
META
4
Microsoft Corporation
MSFT
5
Nvidia Corporation
NVDA
6
test
test
7
Tesla Inc.
TSLA
Find all companies from the Technology sector.
sql ="""SELECT Name FROM Company WHERE Sector = 'Technology';"""run_sql_and_return_df(sql, cnx)
Name
0
Apple Inc.
1
Microsoft Corporation
2
Nvidia Corporation
Get the stock ticker and end of day price for Apple.
sql ="""SELECT Stock_ticker, Eod_price FROM Stock WHERE Stock_ticker = 'AAPL';"""run_sql_and_return_df(sql, cnx)
Stock_ticker
Eod_price
0
AAPL
242.84
Find the companies with IPO dates after 2010
sql ="""SELECT Company.Name, Stock.IPO_dateFROM StockJOIN Company ON Stock.Stock_ticker = Company.TickerWHERE Stock.IPO_date > '2010-01-01'ORDER BY Stock.IPO_date;"""run_sql_and_return_df(sql, cnx)
Name
IPO_date
0
Tesla Inc.
2010-06-29
1
Meta Platforms Inc.
2012-05-18
Retrieve the total revenue and net income for Apple
sql ="""SELECT Total_revenue, Net_income_common_stockholders FROM Income_Statement WHERE Stock_ticker = (SELECT Stock_ticker FROM Company WHERE Ticker = 'AAPL');"""run_sql_and_return_df(sql, cnx)
Total_revenue
Net_income_common_stockholders
0
182527000.0
40269000.0
1
386064000.0
21331000.0
2
31536000.0
690000.0
3
85965000.0
29146000.0
4
365817000.0
94680000.0
5
168088000.0
61271000.0
6
257637000.0
76033000.0
7
469822000.0
33364000.0
8
16675000.0
4332000.0
9
53823000.0
5524000.0
10
117929000.0
39370000.0
11
394328000.0
99803000.0
12
198270000.0
72738000.0
13
282836000.0
59972000.0
14
513983000.0
-2722000.0
15
26914000.0
9752000.0
16
81462000.0
12583000.0
17
116609000.0
23200000.0
18
383285000.0
96995000.0
19
211915000.0
72361000.0
20
307394000.0
73795000.0
21
574785000.0
30425000.0
22
26974000.0
4368000.0
23
96773000.0
14999000.0
24
134902000.0
39098000.0
25
391035000.0
93736000.0
26
245122000.0
88136000.0
27
60922000.0
29760000.0
Get the total debt for all companies in the Consumer Cyclical sector with fiscal year end
sql ="""SELECT Company.Name, Balance_Sheet.Total_debt, Balance_Sheet.Fiscal_year_endFROM Company JOIN Stock ON Company.Ticker = Stock.Stock_tickerJOIN Balance_Sheet ON Stock.Stock_ticker = Balance_Sheet.Stock_tickerWHERE Company.Sector = 'Consumer Cyclical'ORDER BY Balance_Sheet.Fiscal_year_end ASC; """run_sql_and_return_df(sql, cnx)
Name
Total_debt
Fiscal_year_end
0
Amazon.com Inc.
84389000.0
2020-12-31
1
Tesla Inc.
13279000.0
2020-12-31
2
Amazon.com Inc.
116395000.0
2021-12-31
3
Tesla Inc.
8873000.0
2021-12-31
4
Amazon.com Inc.
140118000.0
2022-12-31
5
Tesla Inc.
5748000.0
2022-12-31
6
Amazon.com Inc.
135611000.0
2023-12-31
7
Tesla Inc.
9573000.0
2023-12-31
List all companies with a gross profit greater than 50 million.
sql ="""SELECT Company.Name, Income_Statement.Gross_profit, Income_Statement.Fiscal_year_endFROM CompanyJOIN Income_Statement ON Company.Ticker = Income_Statement.Stock_TickerWHERE Income_Statement.Gross_profit > 50000000ORDER BY Income_Statement.Fiscal_year_end ASC;"""run_sql_and_return_df(sql, cnx)
Name
Gross_profit
Fiscal_year_end
0
Amazon.com Inc.
51500000.0
2020-12-31
1
Meta Platforms Inc.
69273000.0
2020-12-31
2
Alphabet Inc.
97795000.0
2020-12-31
3
Microsoft Corporation
52232000.0
2021-06-30
4
Apple Inc.
152836000.0
2021-09-30
5
Alphabet Inc.
110939000.0
2021-12-31
6
Amazon.com Inc.
66315000.0
2021-12-31
7
Meta Platforms Inc.
95280000.0
2021-12-31
8
Microsoft Corporation
135620000.0
2022-06-30
9
Apple Inc.
170782000.0
2022-09-30
10
Alphabet Inc.
156633000.0
2022-12-31
11
Meta Platforms Inc.
91360000.0
2022-12-31
12
Amazon.com Inc.
67640000.0
2022-12-31
13
Microsoft Corporation
146052000.0
2023-06-30
14
Apple Inc.
169148000.0
2023-09-30
15
Alphabet Inc.
174062000.0
2023-12-31
16
Amazon.com Inc.
93805000.0
2023-12-31
17
Meta Platforms Inc.
108943000.0
2023-12-31
18
Microsoft Corporation
171008000.0
2024-06-30
19
Apple Inc.
180683000.0
2024-09-30
Find the companies with total assets greater than 1 billion.
sql ="""SELECT Company.Name, Balance_Sheet.Fiscal_year_endFROM CompanyJOIN Balance_Sheet ON Company.Ticker = Balance_Sheet.Stock_tickerWHERE Balance_Sheet.Total_assets > 1000000000ORDER BY Balance_Sheet.Fiscal_year_end ASC;"""run_sql_and_return_df(sql, cnx)
Name
Fiscal_year_end
0
no records returned
Get the fiscal year-end date for Microsoft from 2021 to 2024.
sql ="""SELECT Company.Name, Income_Statement.Fiscal_year_endFROM CompanyJOIN Income_Statement ON Company.Ticker = Income_Statement.Stock_tickerWHERE Company.Name = 'Microsoft Corporation'ORDER BY Income_Statement.Fiscal_year_end;"""run_sql_and_return_df(sql, cnx)
Name
Fiscal_year_end
0
Microsoft Corporation
2021-06-30
1
Microsoft Corporation
2022-06-30
2
Microsoft Corporation
2023-06-30
3
Microsoft Corporation
2024-06-30
Find all companies that have a working capital greater than 50 million.
sql ="""SELECT Company.Name, Balance_Sheet.Working_capitalFROM Company JOIN Balance_Sheet ON Balance_Sheet.Stock_Ticker = Company.TickerWHERE Balance_Sheet.Working_capital > 50000000ORDER BY Balance_Sheet.Working_capital"""run_sql_and_return_df(sql, cnx)
Name
Working_capital
0
Meta Platforms Inc.
53405000.0
1
Meta Platforms Inc.
60689000.0
2
Microsoft Corporation
74602000.0
3
Microsoft Corporation
80108000.0
4
Alphabet Inc.
89716000.0
5
Alphabet Inc.
95495000.0
6
Microsoft Corporation
95749000.0
7
Alphabet Inc.
117462000.0
8
Alphabet Inc.
123889000.0
Get the names of companies in the ‘Software Infrastructure’ industry.
sql ="""SELECT Name, Industry FROM Company WHERE Industry = 'Software Infrastructure';"""run_sql_and_return_df(sql, cnx)
Name
Industry
0
Microsoft Corporation
Software Infrastructure
Retrieve the total revenue and cost of revenue for Tesla.
sql ="""SELECT Company.Name, Income_Statement.Total_revenue, Income_Statement.Cost_of_revenue FROM CompanyJOIN Income_Statement ON Income_Statement.Stock_Ticker = Company.Ticker WHERE Company.Ticker = (SELECT Ticker FROM Company WHERE Ticker = 'TSLA');"""run_sql_and_return_df(sql, cnx)
Name
Total_revenue
Cost_of_revenue
0
Tesla Inc.
31536000.0
24906000.0
1
Tesla Inc.
53823000.0
40217000.0
2
Tesla Inc.
81462000.0
60609000.0
3
Tesla Inc.
96773000.0
79113000.0
Find companies that have both net income for common stockholders greater than 10 million.
sql ="""SELECT Company.Name, Income_Statement.Net_income_common_stockholdersFROM CompanyJOIN Income_Statement ON Income_Statement.Stock_Ticker = Company.Ticker WHERE Income_Statement.Net_income_common_stockholders > 10000000;"""run_sql_and_return_df(sql, cnx)
Name
Net_income_common_stockholders
0
Alphabet Inc.
40269000.0
1
Amazon.com Inc.
21331000.0
2
Meta Platforms Inc.
29146000.0
3
Apple Inc.
94680000.0
4
Microsoft Corporation
61271000.0
5
Alphabet Inc.
76033000.0
6
Amazon.com Inc.
33364000.0
7
Meta Platforms Inc.
39370000.0
8
Apple Inc.
99803000.0
9
Microsoft Corporation
72738000.0
10
Alphabet Inc.
59972000.0
11
Tesla Inc.
12583000.0
12
Meta Platforms Inc.
23200000.0
13
Apple Inc.
96995000.0
14
Microsoft Corporation
72361000.0
15
Alphabet Inc.
73795000.0
16
Amazon.com Inc.
30425000.0
17
Tesla Inc.
14999000.0
18
Meta Platforms Inc.
39098000.0
19
Apple Inc.
93736000.0
20
Microsoft Corporation
88136000.0
21
Nvidia Corporation
29760000.0
Retrieve the operating cashflow for Nvidia.
sql ="""SELECT Company.Name, Cash_Flow.Operating_cashflowFROM CompanyJOIN Cash_Flow ON Cash_Flow.Stock_Ticker = Company.TickerWHERE Company.Ticker = (SELECT Ticker FROM Company WHERE Ticker = 'NVDA');"""run_sql_and_return_df(sql, cnx)
Name
Operating_cashflow
0
Nvidia Corporation
5822000.0
1
Nvidia Corporation
9108000.0
2
Nvidia Corporation
5641000.0
3
Nvidia Corporation
28090000.0
Retreive the earnings for all tickers in the fiscal date end of 2022-12-31
Retrieve the stock ticker with fiscal year and cash dividends paid which are not null
sql ="""SELECT Company.Name, Cash_Flow.Cash_dividends_paid, Cash_Flow.Fiscal_year_endFROM CompanyJOIN Cash_Flow ON Cash_Flow.Stock_Ticker = Company.TickerWHERE Cash_Flow.Cash_dividends_paid IS NOT NULLORDER BY Cash_Flow.Fiscal_year_end"""run_sql_and_return_df(sql, cnx)
Name
Cash_dividends_paid
Fiscal_year_end
0
Nvidia Corporation
-395000.0
2021-01-31
1
Microsoft Corporation
-16521000.0
2021-06-30
2
Apple Inc.
-14467000.0
2021-09-30
3
Nvidia Corporation
-399000.0
2022-01-31
4
Microsoft Corporation
-18135000.0
2022-06-30
5
Apple Inc.
-14841000.0
2022-09-30
6
Nvidia Corporation
-398000.0
2023-01-31
7
Microsoft Corporation
-19800000.0
2023-06-30
8
Apple Inc.
-15025000.0
2023-09-30
9
Nvidia Corporation
-395000.0
2024-01-31
10
Microsoft Corporation
-21771000.0
2024-06-30
11
Apple Inc.
-15234000.0
2024-09-30
Retrieve the stock ticker and free cash flow where free cash flow is greater than 50,000,000
For future work, we would want to connect our database to a working API instead of manual input of data. Additionally, the integration between the front end and back end could be done using different tools. React is a great tool, however, we could have also implemented Bootstrap for consistent styling. Another consideration for the future is to include security enhancements to the system.
Reflection
The project went well. We didn’t achieve what we wanted to originally build, but we got some things working which satisfied the requirements. We had little time to insert the values into our database, and I wanted to use an API to do it dynamically, but unfortunately it didn’t work.